package br.com.skytef.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpSession;

import br.com.skytef.entity.TarifaItensEntity;
import br.com.skytef.entity.TariffEntity;
import br.com.skytef.jdbc.ConnectionFactory;
import br.com.skytef.utils.utils;

public class TariffDao {
	private static String erro;
	public Connection connection;

	public TariffDao() {
		this.connection = new ConnectionFactory().getConnection();
	}

	public TariffDao(HttpSession session) {
		this.connection = utils.recuperaDBConnection(session);
	}

	public TariffDao(Connection con) {
		this.connection = con;
	}

	public Connection getConnection() {
		return connection;
	}

	public static String getErro() {
		return erro;
	}

	/*-----------------------------------------------------------------------------------------------
	 * INSERT
	 -----------------------------------------------------------------------------------------------*/
	
	public TariffEntity insertTarifa(TariffEntity tarifa) throws Exception {
		try {
			String query = "INSERT INTO tarifa(idVeiculo_tipo,idEstabelecimento,descricaoTarifa,tempo,valor) VALUES (?,?,?,?,?);";

			PreparedStatement stmt = connection.prepareStatement(query,
					PreparedStatement.RETURN_GENERATED_KEYS);

			stmt.setInt(1, tarifa.getIdVeiculo_tipo());
			stmt.setInt(2, tarifa.getIdEstabelecimento());
			stmt.setString(3, tarifa.getDescricaoTarifa());
			stmt.setString(4, tarifa.getTempo());
			stmt.setDouble(5, tarifa.getValor());
			
			stmt.executeUpdate();
			stmt.close();

		} catch (SQLException e) {
			throw new Exception(e);
		} catch (Exception e) {
			throw new Exception(e);
		}

		return tarifa;
	}

	/*-----------------------------------------------------------------------------------------------
	 * UPDATE
	 -----------------------------------------------------------------------------------------------*/

	public TariffEntity updateTarifa(TariffEntity tarifa) throws SQLException {
		try {
			String query = "UPDATE tarifa SET idVeiculo_tipo=?,descricaoTarifa=?, tempo=?, valor=?, "
					+ "idEstabelecimento=? WHERE idTarifa=? ;";
			PreparedStatement stmt = connection.prepareStatement(query);
			stmt.setInt(1, tarifa.getIdVeiculo_tipo());
			stmt.setString(2, tarifa.getDescricaoTarifa());
			stmt.setString(3, tarifa.getTempo());
			stmt.setDouble(4, tarifa.getValor());
			stmt.setInt(5, tarifa.getIdEstabelecimento());
			stmt.setInt(6, tarifa.getIdTarifa());
			stmt.executeUpdate();

			stmt.close();
		} catch (SQLException e) {
			erro = e.toString();
		} catch (Exception e) {
			erro = e.toString();
		}
		return tarifa;
	}
		
	/*-----------------------------------------------------------------------------------------------
	 * SELECT
	 -----------------------------------------------------------------------------------------------*/
	public List<TariffEntity> buscarTodos() throws SQLException {
		List<TariffEntity> tarifas = null;
		Connection connection = new ConnectionFactory().getConnection();
		connection.setAutoCommit(true);
		PreparedStatement stmt = connection.prepareStatement(this.getQrySelectTodos());
		tarifas = execSelectMany(stmt);
		return tarifas; 
	}
	
	//---------- Teste -------------
	public List<TariffEntity> buscarTarifas() throws SQLException {
		List<TariffEntity> tarifas = null;
		Connection connection = new ConnectionFactory().getConnection();
		connection.setAutoCommit(true);
		PreparedStatement stmt = connection.prepareStatement("Select * from tarifa;");
		tarifas = execSelectMany(stmt);
		return tarifas;
	}
	
	public TariffEntity buscarPorId(int id) throws SQLException {
		TariffEntity tarifa;
		Connection connection = new ConnectionFactory().getConnection();
		connection.setAutoCommit(true);
		PreparedStatement stmt = connection.prepareStatement("Select * from tarifa where idTarifa = ?;");
		stmt.setInt(1, id);
		tarifa = execSelectOne(stmt);
		return tarifa; 
	}
	
	//
	public TarifaItensEntity buscarTarifaPorId(int id) throws SQLException {
		TarifaItensEntity tarifa;
		Connection connection = new ConnectionFactory().getConnection();
		connection.setAutoCommit(true);
		PreparedStatement stmt = connection.prepareStatement("Select * from tarifa where idTarifa = ?;");
		stmt.setInt(1, id);
		tarifa = exeSelectOne(stmt);
		return tarifa; 
	}
	
	
	/*-----------------------------------------------------------------------------------------------
	 * SELECT exec
	 -----------------------------------------------------------------------------------------------*/
	public List<TariffEntity> execSelectMany(PreparedStatement stmt) {
		List<TariffEntity> tarifas = null;
		TariffEntity tarifa;
		try {
			ResultSet rs = stmt.executeQuery();
			if (rs.next()) {
				tarifas = new ArrayList<TariffEntity>();
				do {
					tarifa = new TariffEntity();
					tarifa.setIdTarifa(rs.getInt("idTarifa"));
					tarifa.setIdVeiculo_tipo(rs.getInt("idVeiculo_tipo"));
					tarifa.setDescricaoTarifa(rs.getString("descricaoTarifa"));
					tarifa.setIdEstabelecimento(rs.getInt("idEstabelecimento"));
					tarifa.setTempo(rs.getString("tempo"));
					tarifa.setValor(rs.getDouble("valor"));

					tarifas.add(tarifa);
				} while (rs.next());
			}
			rs.close();
			connection.close();
		} catch (SQLException e) {
			System.out.println("SQL erro: " + e);
		} catch (Exception e) {
			System.out.println("Geral erro: " + e);
		}
		return tarifas;
	}

	public TariffEntity execSelectOne(PreparedStatement stmt) {
		TariffEntity tarifa = null;
		try {
			ResultSet rs = stmt.executeQuery();
			if (rs.next()) {
				tarifa = new TariffEntity();
				tarifa.setIdTarifa(rs.getInt("idTarifa"));
				tarifa.setIdVeiculo_tipo(rs.getInt("idVeiculo_tipo"));
				tarifa.setDescricaoTarifa(rs.getString("descricaoTarifa"));
				tarifa.setIdEstabelecimento(rs.getInt("idEstabelecimento"));
				tarifa.setTempo(rs.getString("tempo"));
				tarifa.setValor(rs.getDouble("valor"));
			}
			rs.close();
			connection.close();
		} catch (SQLException e) {
			System.out.println("SQL erro: " + e);
		} catch (Exception e) {
			System.out.println("Geral erro: " + e);
		}
		return tarifa;
	}
	
	// busca por cod
	public TarifaItensEntity exeSelectOne(PreparedStatement stmt) {
		TarifaItensEntity tarifa = null;
		try {
			ResultSet rs = stmt.executeQuery();
			if (rs.next()) {
				tarifa = new TarifaItensEntity();
				tarifa.setIdTarifa(rs.getInt("idTarifa"));
				tarifa.setDescricao(rs.getString("descricaoTarifa"));
				tarifa.setIdVeiculoTipo(rs.getInt("idVeiculo_tipo"));
				tarifa.setDescricao(rs.getString("descricao"));
				tarifa.setIdEstabelecimento(rs.getInt("idEstabelecimento"));
				tarifa.setRazaoSocial(rs.getString("razao_social"));
				tarifa.setTempo(rs.getString("tempo"));
				tarifa.setValor(rs.getDouble("valor"));
			}
			rs.close();
			connection.close();
		} catch (SQLException e) {
			System.out.println("SQL erro: " + e);
		} catch (Exception e) {
			System.out.println("Geral erro: " + e);
		}
		return tarifa;
	}
		
	public TariffEntity getTarifaPorId(int idTarifa) throws SQLException {
		Connection connection = new ConnectionFactory().getConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement(this.getQrySelectById());
			stmt.setInt(1, idTarifa);
			ResultSet rs = stmt.executeQuery();
			
			if (rs.next()) {

				TariffEntity tarifa = new TariffEntity();
				
				tarifa.setIdTarifa(rs.getInt("idTarifa"));
				tarifa.setIdVeiculo_tipo(rs.getInt("idVeiculo_tipo"));
				tarifa.setIdEstabelecimento(rs.getInt("idEstabelecimento"));
				tarifa.setDescricaoTarifa(rs.getString("descricaoTarifa"));
				tarifa.setTempo(rs.getString("tempo"));
				tarifa.setValor(rs.getDouble("valor"));				

				rs.close();
				stmt.close();
				connection.close();
				return tarifa;
			} else {
				rs.close();
				stmt.close();
				connection.close();
				return null;
			}

		} catch (SQLException e) {
			connection.rollback();
			connection.close();
			throw new RuntimeException(e);
		}

	}
	
	private String getQrySelectById() {
		StringBuffer sql = new StringBuffer();

		sql.append("SELECT 	tar.idtarifa, "); 
		sql.append("	   	veiculo.idveiculo_tipo, "); 
		sql.append("		veiculo.descricao, "); 
		sql.append("   		est.idestabelecimento, "); 
		sql.append("   		est.razao_social, ");
		sql.append("		tar.descricaoTarifa, ");
		sql.append("		tar.tempo, ");
		sql.append("		tar.valor ");
		sql.append("FROM   	tarifa tar "); 
		sql.append("LEFT JOIN veiculo_tipo veiculo "); 
		sql.append("ON 		tar.idtarifa = veiculo.idveiculo_tipo "); 
		sql.append("LEFT JOIN estabelecimento est ");
		sql.append("ON 		tar.idestabelecimento = est.idestabelecimento "); 
		sql.append("WHERE  	tar.idtarifa = ?; ");
		
		return sql.toString();
	}
				
	// teste 
	private String getQrySelectTodos() {
		StringBuffer sql = new StringBuffer();

		sql.append("SELECT 	tar.idtarifa, "); 
		sql.append("	   	veiculo.idveiculo_tipo, "); 
		sql.append("		veiculo.descricao, "); 
		sql.append("   		est.idestabelecimento, "); 
		sql.append("   		est.razao_social, ");
		sql.append("		tar.descricaoTarifa, ");
		sql.append("		tar.tempo, ");
		sql.append("		tar.valor ");
		sql.append("FROM   	tarifa tar "); 
		sql.append("LEFT JOIN veiculo_tipo veiculo "); 
		sql.append("ON 		tar.idtarifa = veiculo.idveiculo_tipo "); 
		sql.append("LEFT JOIN estabelecimento est ");
		sql.append("ON 		tar.idestabelecimento = est.idestabelecimento ;"); 
		
		return sql.toString();
	}
	
	public List<TariffEntity> buscarTudo() throws SQLException {
		List<TariffEntity> tarifas = null;
		Connection connection = new ConnectionFactory().getConnection();
		connection.setAutoCommit(true);
		PreparedStatement stmt = connection.prepareStatement(this.getQrySelectTodos());
		tarifas = execSelectTodos(stmt);
		return tarifas; 
	}
	
	public List<TariffEntity> execSelectTodos(PreparedStatement stmt) {
		List<TariffEntity> tarifas = null;
		TariffEntity tarifa;
		try {
			ResultSet rs = stmt.executeQuery();
			if (rs.next()) {
				tarifas = new ArrayList<TariffEntity>();
				do {
					tarifa = new TariffEntity();
					tarifa.setIdTarifa(rs.getInt("idTarifa"));
					tarifa.setIdVeiculo_tipo(rs.getInt("idVeiculo_tipo"));
					tarifa.setIdEstabelecimento(rs.getInt("idEstabelecimento"));
					tarifa.setDescricaoTarifa(rs.getString("descricaoTarifa"));
					tarifa.setTempo(rs.getString("tempo"));
					tarifa.setValor(rs.getDouble("valor"));

					tarifas.add(tarifa);
				} while (rs.next());
			}
			rs.close();
			connection.close();
		} catch (SQLException e) {
			System.out.println("SQL erro: " + e);
		} catch (Exception e) {
			System.out.println("Geral erro: " + e);
		}
		return tarifas;
	}
	
	public TariffEntity buscaPorDescricao(String descricao) throws SQLException {
		String qry = "select * from tarifa where descricaoTarifa = '"  + descricao + "';";
		TariffEntity tarifa = this.exeQuery(qry);
		return tarifa;
	}
	
	public TariffEntity getTarifa(int idTarifa) throws SQLException {
		TariffEntity tarifa = null;
		
		String qry = "SELECT * from tarifa where idTarifa = " + idTarifa;
		TariffEntity tar = this.exeQuery(qry);
		
		return tar;
		
	}
	
	/*
	 * Executa qualquer query	
	 */
	public TariffEntity exeQuery(String sql) {
		TariffEntity tarifa = new TariffEntity();
		
		try {
			Connection conn = new ConnectionFactory().getConnection();;
			PreparedStatement stmt = conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			
			if (rs.next()) {
				
				tarifa.setIdTarifa(rs.getInt("idTarifa"));
				tarifa.setIdVeiculo_tipo(rs.getInt("idVeiculo_tipo"));
				tarifa.setDescricaoTarifa(rs.getString("descricaoTarifa"));
				tarifa.setIdEstabelecimento(rs.getInt("idEstabelecimento"));
				tarifa.setTempo(rs.getString("tempo"));
				tarifa.setValor(rs.getDouble("valor"));
									
			}
			rs.close();
			stmt.close();
			return tarifa;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	

}
